﻿Imports System.Data.SqlClient
Public Class dbAdvSource
    Dim Var As New varDeclare

    Public Function getGroupsForAdvList() As DataSet
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.SUISConn)

        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = "SELECT GROUPS 'Code', GROUP_DESCRIPTION 'Name', GROUP_STATUS_CODE 'Status' FROM GROUP_ITEM WITH(NOLOCK) WHERE COMP = @CompID"
        command.Parameters.Add("@CompID", SqlDbType.VarChar).Value = Var.CompID
        adapter = New SqlDataAdapter(command)
        adapter.Fill(ds)
        connection.Close()
        Return ds
    End Function

    Public Function getItemCodeForAdvByGroups(Groups As String) As DataSet
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.SUISConn)

        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = "SELECT (GI.GROUPS + '-' + IC.Code) 'Code', IC.IC_DESCRIPTION 'Name',IC. STATUS 'Status' FROM ITEM_CODE IC WITH(NOLOCK) INNER JOIN GROUP_ITEM GI WITH(NOLOCK) ON GI.GROUPS = IC.GROUPS AND GI.GROUPS = ISNULL(@Groups, GI.GROUPS) WHERE ISNULL(IC.COMP, @CompID) = @CompID"
        command.Parameters.Add("@CompID", SqlDbType.VarChar).Value = Var.CompID
        If (Groups = "") Then
            command.Parameters.Add("@Groups", SqlDbType.VarChar).Value = DBNull.Value
        Else
            command.Parameters.Add("@Groups", SqlDbType.VarChar).Value = Groups
        End If
        adapter = New SqlDataAdapter(command)
        adapter.Fill(ds)
        connection.Close()
        Return ds
    End Function

    Public Function GetPrincipalForAdv() As DataSet
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.SUISConn)
        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = "SELECT DISTINCT PRINCIPAL_CODE Code, PRINCIPAL_COMPANY Name, ISNULL(TERM_PAYMENT, 0) Term FROM PRINCIPAL WITH(NOLOCK) WHERE COMP = @CompID ORDER BY PRINCIPAL_CODE"
        command.Parameters.Add("@CompID", SqlDbType.VarChar).Value = Var.CompID
        adapter = New SqlDataAdapter(command)
        adapter.Fill(ds)
        connection.Close()
        Return ds
    End Function

    Public Function GetIncotermForAdv() As DataSet
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.SUISConn)
        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = "SELECT INV_INCOTERM_CODE 'Code', INV_INCOTERM_DESC 'Name' FROM T_INV_INCOTERMS WITH(NOLOCK)"
        command.Parameters.Add("@CompID", SqlDbType.VarChar).Value = Var.CompID
        adapter = New SqlDataAdapter(command)
        adapter.Fill(ds)
        connection.Close()
        Return ds
    End Function

    Public Function GetUoMForAdv() As DataSet
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.SUISConn)
        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = "SELECT DISTINCT INV_UOM_BASE 'Code' FROM T_INV_UNIT_OF_MEASURE WITH(NOLOCK) WHERE INV_UOM_BASE IS NOT NULL"
        command.Parameters.Add("@CompID", SqlDbType.VarChar).Value = Var.CompID
        adapter = New SqlDataAdapter(command)
        adapter.Fill(ds)
        connection.Close()
        Return ds
    End Function

    Public Function GetRemarkCostForAdv() As DataSet
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.SUISConn)
        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = "SELECT DISTINCT INV_COSTING_CODE 'Code', INV_COSTING_DESC 'Name' FROM T_INV_COSTING WITH(NOLOCK)"
        command.Parameters.Add("@CompID", SqlDbType.VarChar).Value = Var.CompID
        adapter = New SqlDataAdapter(command)
        adapter.Fill(ds)
        connection.Close()
        Return ds
    End Function
End Class
